#!/usr/bin/env python
from lib.format_time import *
from lib.HandleMysql import *
from lib.switch_code import *
from lib.load_data import *
from shop_conn import *
from cate_city_tools import *
import json
import sys
import re
reload(sys)
sys.setdefaultencoding('utf-8')

def get_newest_order():
    #conn = set_stats_conn()
    #query = "select ifnull(max(order_id),0) from order_info"
    #items = conn.get_results(query)
    #maxid = items[0][0]
    #conn.close()
    return 0

def get_newest_order_items():
    #conn = set_stats_conn()
    #query = "select ifnull(max(item_id),0) from order_items"
    #items = conn.get_results(query)
    #maxid = items[0][0]
    #conn.close()
    return 0

def get_newest_order_ticket():
    #conn = set_stats_conn()
    #query = "select ifnull(max(ticket_id),0) from order_ticket"
    #items = conn.get_results(query)
    #maxid = items[0][0]
    #conn.close()
    return 0

def get_order_data(date):
    
    print "get_order_data bigin : %s"%date
    
    conn = set_order_conn()
    stat_conn = set_stats_conn()
    prod_conn = set_product_conn()
    shop_conn = set_shop_conn()
    stat_table = "order_info"
    line_list = []
    #refer dict
    refers = {'1':'duanzufang','3':'zhoubianyou','102':'xiaozhu','108':'su8'}
    
    max_order_id = get_newest_order()
    print max_order_id
    #get data
    query = "select orderid,buyerid,sellerid,refer,ordertitle,cityid,createdate,num,totalfee,discountfee,adjustfee,ordertotal,paydate,state,phone,buyernick from t_order where refer<>4 and orderid>%s order by orderid asc" %max_order_id
    items = conn.get_results(query)
    for item in items:
        order_id = item[0]            
        buyer_id = item[1]
        shop_id = item[2]
        order_refer = item[3]
        order_title = item[4]
        source_city_id = item[5]
        
        create_time = item[6]
        product_count = item[7]
        
        total_amount = item[8]
        dis_amount = item[9]
        adjust_amount = item[10]
        pay_amount = item[11]
        
        pay_time = item[12]
        order_state = item[13]
        phone = item[14]
        buyer_nick = item[15]
        
        #shop _info
        shop_name = ''
        shop_type = 0
        query_tmp = "select shopname,freeOrPay from shop where id=%s"%shop_id
        items_tmp = shop_conn.get_results(query_tmp)
        if len(items_tmp)>0:
            shop_name = items_tmp[0][0]
            shop_type = items_tmp[0][1]
        
        #product id
        product_id = 0
        product_count = 0
        product_price = 0
        query_tmp = "select productid,num,price from t_orderproduct where orderid=%s limit 1"%order_id
        items_tmp = conn.get_results(query_tmp)
        if len(items_tmp) > 0 :
            product_id = items_tmp[0][0]
            product_count = items_tmp[0][1]
            product_price = items_tmp[0][2]
        
        price = product_price
        #product count
        #print refers
        if refers.has_key('%s'%order_refer):
            #print "has"
            query_tmp = "select times from t_orderbook where orderid=%s"%order_id
            items_tmp = conn.get_results(query_tmp)
            #product_id = items_tmp[0][0]
            product_count = items_tmp[0][0]
            if product_count == 0:
                product_count = 1
            
            price = product_price/product_count
        
        
        #consume info
        consume_product_count = 0
        consume_amount= 0
        query_tmp = "select count(1),ifnull(sum(b.price),0) from t_orderticket a left join t_orderproduct b on a.orderproductid=b.id where a.refer<>4 and a.orderid=%s and a.lifestate=3 "%order_id
        items_tmp = conn.get_results(query_tmp)
        cnt = items_tmp[0][0]
        if cnt > 0:
            if refers.has_key('%s'%order_refer):
                consume_product_count = product_count
            else:
                consume_product_count =cnt
        consume_amount = items_tmp[0][1]
        
        #order source city
        source_city_id,source_city_name = get_city(source_city_id)
        
        cate_id = 0;
        local= ''
        param = ''
        query_tmp = "select cateid,local,param from t_product where productid=%s"%product_id
        items_tmp = prod_conn.get_results(query_tmp)
        if len(items_tmp) > 0 :
            cate_id =  items_tmp[0][0]
            local = items_tmp[0][1]
            #product_name = items_tmp[0][2]
            param = items_tmp[0][2]
            #price = items_tmp[0][3]
        
        #cate
        #print cate_id
        dc = get_full_category(stat_conn,cate_id)    
        cat1 = 0
        cat1name = ''
        cat2 = 0
        cat2name=''
        cat3 = 0
        cat3name=''
        if dc != None:
            if len(dc) > 0:
                cat1 = dc[0][0]
                cat1name = dc[0][1]
            if len(dc) > 1:
                cat2 = dc[1][0]
                cat2name=dc[1][1]
            if len(dc) > 2:
                cat3 = dc[2][0]
                cat3name=dc[2][1]
       
       #city
        city_id = 0
        city_name = ''
        area_id = 0
        area_name = ''
        local_id= 0
        local_name = ''
        if local != None and local !='':
            city = json.loads(local)[0]['L']
            #print city
            dc = get_citys(city)
            if dc != None:
                if len(dc) > 0:
                    city_id = dc[0][0]
                    city_name = dc[0][1]
                if len(dc)>1:
                    area_id = dc[1][0]
                    area_name = dc[1][1]
                if len(dc)>2:
                    local_id= dc[2][0]
                    local_name = dc[2][1]
        
        #pay info
        pay_type = -1
        pay_state = 0
        query_tmp = "select paytype,paystate from t_orderpay where orderid=%s"%order_id
        items_tmp = conn.get_results(query_tmp)
        if len(items_tmp)>0:
            pay_type =  items_tmp[0][0]
            pay_state = items_tmp[0][1]
        
        #refund info
        refund_product_count =0
        refund_amount = 0
        query_tmp = "select ifnull(sum(refundamount),0) from refundrequest where orderid=%s and refundstate=3"%order_id
        items_tmp = conn.get_results(query_tmp)
        if len(items_tmp)>0:
            refund_amount =  items_tmp[0][0]
            
        if price>0:
            refund_product_count = refund_amount/price        
        if shop_name != None:
            shop_name = shop_name.replace("'","")
            shop_name = shop_name.replace("\"","")
            shop_name = shop_name.replace("\\"," ")
        
        param = ["%s"%order_id,"%s"%buyer_id,"%s"%shop_id,"%s"%shop_name,"%s"%order_refer,"%s"%order_title,"%s"%cat1,"%s"%cat1name,"%s"%cat2,"%s"%cat2name,"%s"%cat3,"%s"%cat3name,"%s"%city_id,"%s"%city_name,"%s"%area_id,"%s"%area_name,"%s"%local_id,"%s"%local_name,"%s"%source_city_id,"%s"%source_city_name,"%s"%create_time,"%s"%price,"%s"%product_count,"%s"%total_amount,"%s"%dis_amount,"%s"%adjust_amount,"%s"%pay_amount,"%s"%pay_time,"%s"%pay_type,"%s"%pay_state,"%s"%refund_product_count,"%s"%refund_amount,"%s"%0,"%s"%0,"%s"%0,"%s"%0,"%s"%consume_product_count,"%s"%consume_amount,"%s"%order_state,"%s"%buyer_nick,"%s"%phone,"%s"%shop_type]
        line_list.append('\t'.join(param))
        #try:
        #    stat_conn.insert_record(stat_table,param)
        #except Exception,e:
        #    continue
        
    cont = '\n'.join(line_list)
    path = "./order_data/"
    file_name = "order_info_%s.txt"%date.replace('-',"")
    #print path
    writeTextToFile(path,file_name,"w",cont)
    
    query = "truncate table order_info;"
    stat_conn.execute_sql(query)
    load_data(stat_conn,path+file_name,stat_table,'\t')
    conn.close()
    prod_conn.close()
    stat_conn.close()
    shop_conn.close()
    pass

def get_order_items_data(date):
    
    print "get_order_items_data bigin : %s"%date
    
    conn = set_order_conn()
    stat_conn = set_stats_conn()
    prod_conn = set_product_conn()
    shop_conn = set_shop_conn()
    stat_table = "order_items"
    line_list = []
    #refer dict
    refers = {'1':'duanzufang','3':'zhoubianyou','102':'xiaozhu','108':'su8'}
    
    max_order_items_id = get_newest_order_items()
    print max_order_items_id
    #get data
    query = "select id,orderid,productrefer,productid,producttitle,sellerid,buyerid,refunddate,refundstate,num,price,turnoverprice,remarkstate,resumestate,settlementprice,settlementstate,settlementtype,settlementexplain,ifnull(settlementaccount,0),ifnull(settlementtime,''),rightsstate,brefund,businesstype,pricemode from t_orderproduct where productrefer<>4 and id>%s" %max_order_items_id
    items = conn.get_results(query)
    for item in items:
        item_id = item[0]
        order_id = item[1]
        order_refer = item[2]
        product_id = item[3]
        product_name = item[4]
        shop_id = item[5]
        buyer_id = item[6]
        refund_time = item[7]
        refund_state = item[8]
        
        product_count = item[9]
        product_price = item[10]
        pay_amount = item[11]
        
        remark_state = item[12]
        resume_state = item[13]
        settle_price = item[14]
        settle_state = item[15]
        settle_type = item[16]
        settle_explain = item[17]
        settle_account = item[18]
        settle_time = item[19]
        
        rights_state = item[20]
        be_refund = item[21]
        business_type = item[22]
        price_mode = item[23]
        
        price = product_price
        #product count
        #print refers
        if refers.has_key('%s'%order_refer):
            #print "has"
            query_tmp = "select times from t_orderbook where orderid=%s"%order_id
            items_tmp = conn.get_results(query_tmp)
            #product_id = items_tmp[0][0]
            product_count = items_tmp[0][0]
            if product_count == 0:
                product_count = 1
            
            price = product_price/product_count
        
        #price cate city
        cate_id = 0
        local=''
        param=''
        query_tmp = "select cateid,local,param from t_product where productid=%s"%product_id
        items_tmp = prod_conn.get_results(query_tmp)
        if len(items_tmp)>0:
            cate_id =  items_tmp[0][0]
            local = items_tmp[0][1]
            #product_name = items_tmp[0][2]
            param = items_tmp[0][2]

        
        #cate
        #print cate_id
        dc = get_full_category(stat_conn,cate_id)      
        cat1 = 0
        cat1name = ''
        cat2 = 0
        cat2name=''
        cat3 = 0
        cat3name=''
        if dc != None:
            if len(dc) > 0:
                cat1 = dc[0][0]
                cat1name = dc[0][1]
            if len(dc) > 1:
                cat2 = dc[1][0]
                cat2name=dc[1][1]
            if len(dc) > 2:
                cat3 = dc[2][0]
                cat3name=dc[2][1]
       
       #city
        city_id = 0
        city_name = ''
        area_id = 0
        area_name = ''
        local_id= 0
        local_name = ''
        if local != None and local !='':
            city = json.loads(local)[0]['L']
            #print city
            dc = get_citys(city)
            if dc != None:
                if len(dc) > 0:
                    city_id = dc[0][0]
                    city_name = dc[0][1]
                if len(dc)>1:
                    area_id = dc[1][0]
                    area_name = dc[1][1]
                if len(dc)>2:
                    local_id= dc[2][0]
                    local_name = dc[2][1]
        
        param = ["%s"%item_id,"%s"%order_id,"%s"%order_refer,"%s"%product_id,product_name,"%s"%shop_id,"%s"%buyer_id,"%s"%refund_time,"%s"%refund_state,"%s"%product_count,"%s"%price,"%s"%pay_amount,"%s"%remark_state,"%s"%resume_state,"%s"%settle_price,"%s"%settle_state,"%s"%settle_type,"%s"%settle_explain,"%s"%settle_account,"%s"%settle_time,"%s"%rights_state,"%s"%be_refund,"%s"%business_type,"%s"%price_mode,"%s"%cat1,cat1name,"%s"%cat2,cat2name,"%s"%cat3,cat3name,"%s"%city_id,city_name,"%s"%area_id,area_name,"%s"%local_id,local_name]
        line_list.append('\t'.join(param))
        #try:
        #    stat_conn.insert_record(stat_table,param)
        #except Exception,e:
        #    continue
        
    cont = '\n'.join(line_list)
    path = "./order_data/"
    file_name = "order_items_%s.txt"%date.replace('-',"")
    writeTextToFile(path,file_name,"w",cont)
    
    query = "truncate table order_items;"
    stat_conn.execute_sql(query)
    load_data(stat_conn,path+file_name,stat_table,'\t')
    conn.close()
    prod_conn.close()
    stat_conn.close()
    shop_conn.close()
    pass

def get_order_ticket_data(date):
    
    print "get_order_ticket_data bigin : %s"%date
    
    conn = set_order_conn()
    stat_conn = set_stats_conn()
    prod_conn = set_product_conn()
    shop_conn = set_shop_conn()
    stat_table = "order_ticket"
    line_list = []
    #refer dict
    refers = {'1':'duanzufang','3':'zhoubianyou','102':'xiaozhu','108':'su8'}
    
    max_order_ticket_id = get_newest_order_ticket()
    print max_order_ticket_id
    #get data
    query = "select id,orderid,refer,ticketscode,orderproductid,sellerid,buyerid,productid,expiredate,consumedate,sendmsgcount,downnum,booknum,bookid,bookexpireddate,lifestate,tickettype,errorcount,lastsendtime from t_orderticket where refer<>4 and id>%s" %max_order_ticket_id
    items = conn.get_results(query)
    for item in items:
        ticket_id = item[0]
        order_id = item[1]
        order_refer = item[2]        
        ticket_code = item[3]
        item_id = item[4]
        
        shop_id = item[5]
        buyer_id = item[6]
        product_id = item[7]
        expire_time = item[8]
        
        consume_time = item[8]
        send_msg_count = item[10]
        down_num = item[11]
        book_num = item[12]
        if book_num == None:
            book_num = 0
            
        book_id = item[13]
        book_expire_time = item[14]
        if book_expire_time == None:
            book_expire_time = '0000-00-00'
        
        life_state = item[15]
        ticket_type = item[16]
        
        error_count = item[17]
        if error_count == None:
            error_count =0
            
        last_end_time = item[18]
        if last_end_time == None:
            last_end_time = '0000-00-00'
        
        #product_count = item[9]
        #product_price = item[10]
        #pay_amount = item[11]
        
        product_count = 0.0
        product_price = 0.0
        ticket_amount = 0.0
        refund_time = '0000-00-00'
        refund_state = 0
        query_tmp = "select num,price,turnoverprice,refunddate,refundstate from t_orderproduct where id=%s"%item_id
        items_tmp = conn.get_results(query_tmp)
        if len(items_tmp) > 0:
            product_count = items_tmp[0][0]
            product_price = items_tmp[0][1]
            ticket_amount = items_tmp[0][2]
            refund_time = items_tmp[0][3]
            refund_state = items_tmp[0][4]
        
        if refund_time == None:
            refund_time = '0000-00-00'
        
        if ticket_amount == 0:
            ticket_amount = product_count * product_price
        
        price = product_price
        #product count
        #print refers
        if refers.has_key('%s'%order_refer):
            #print "has"
            query_tmp = "select times from t_orderbook where orderid=%s"%order_id
            items_tmp = conn.get_results(query_tmp)
            #product_id = items_tmp[0][0]
            if len(items_tmp) > 0:
                product_count = items_tmp[0][0]
                if product_count == 0:
                    product_count = 1
            
            price = product_price/product_count
        
        
        #price cate city
        cate_id = 0
        local=''
        param=''
        product_name=''
        query_tmp = "select cateid,local,param,title from t_product where productid=%s"%product_id
        items_tmp = prod_conn.get_results(query_tmp)
        if len(items_tmp)>0:
            cate_id =  items_tmp[0][0]
            local = items_tmp[0][1]
            #product_name = items_tmp[0][2]
            param = items_tmp[0][2]
            product_name = items_tmp[0][3]

        
        #cate
        #print cate_id
        dc = get_full_category(stat_conn,cate_id)      
        cat1 = 0
        cat1name = ''
        cat2 = 0
        cat2name=''
        cat3 = 0
        cat3name=''
        if dc != None:
            if len(dc) > 0:
                cat1 = dc[0][0]
                cat1name = dc[0][1]
            if len(dc) > 1:
                cat2 = dc[1][0]
                cat2name=dc[1][1]
            if len(dc) > 2:
                cat3 = dc[2][0]
                cat3name=dc[2][1]
       
       #city
        city_id = 0
        city_name = ''
        area_id = 0
        area_name = ''
        local_id= 0
        local_name = ''
        if local != None and local !='':
            city = json.loads(local)[0]['L']
            #print city
            dc = get_citys(city)
            if dc != None:
                if len(dc) > 0:
                    city_id = dc[0][0]
                    city_name = dc[0][1]
                if len(dc)>1:
                    area_id = dc[1][0]
                    area_name = dc[1][1]
                if len(dc)>2:
                    local_id= dc[2][0]
                    local_name = dc[2][1]
        
        param = ["%s"%ticket_id,"%s"%order_id,"%s"%order_refer,ticket_code,"%s"%item_id,"%s"%shop_id,"%s"%buyer_id,"%s"%product_id,product_name,"%s"%price,"%s"%ticket_amount,"%s"%expire_time,"%s"%consume_time,"%s"%send_msg_count,"%s"%down_num,"%s"%book_num,"%s"%book_id,"%s"%book_expire_time,"%s"%cat1,cat1name,"%s"%cat2,cat2name,"%s"%cat3,cat3name,"%s"%city_id,city_name,"%s"%area_id,area_name,"%s"%local_id,local_name,"%s"%life_state,"%s"%refund_time,"%s"%refund_state,"%s"%ticket_type,"%s"%error_count,"%s"%last_end_time]
        line_list.append('\t'.join(param))
        
    cont = '\n'.join(line_list)
    path = "./order_data/"
    file_name = "order_ticket_%s.txt"%date.replace('-',"")
    writeTextToFile(path,file_name,"w",cont)
    
    query = "truncate table order_ticket;"
    stat_conn.execute_sql(query)
    load_data(stat_conn,path+file_name,stat_table,'\t')
    conn.close()
    prod_conn.close()
    stat_conn.close()
    shop_conn.close()
    pass

def get_order_book_data(date):
    print "get_order_book_data bigin : %s"%date    
    conn = set_order_conn()
    stat_conn = set_stats_conn()
    prod_conn = set_product_conn()
    #shop_conn = set_shop_conn()
    stat_table = "order_book"
    line_list = []
    refers = {'1':'duanzufang','3':'zhoubianyou','102':'xiaozhu','108':'su8'}
    max_order_book_id = 0
    
    #get data
    query = "select id,buyerid,sellerid,orderid,productid,SKUID,num,unit,refer,bookstate,operationtime,starttime,endtime,times,bookdesc,modifystate from t_orderbook where refer<>4 and id>%s" %max_order_book_id
    items = conn.get_results(query)
    for item in items:
        book_id = item[0]
        buyer_id = item[1]
        shop_id = item[2]
        order_id = item[3]
        product_id = item[4]
        SKUID = item[5]
        book_num = item[6]
        unit = item[7]
        order_refer = item[8]
        book_state = item[9]
        operation_time = item[10]
        start_time = item[11]
        end_time = item[12]
        times = item[13]
        book_desc = item[14]
        modify_state = item[15]
        
        #price cate city
        cate_id = 0
        local=''
        param=''
        product_name=''
        product_price = 0
        query_tmp = "select cateid,local,param,title,minprice from t_product where productid=%s"%product_id
        items_tmp = prod_conn.get_results(query_tmp)
        if len(items_tmp)>0:
            cate_id =  items_tmp[0][0]
            local = items_tmp[0][1]
            #product_name = items_tmp[0][2]
            param = items_tmp[0][2]
            product_name = items_tmp[0][3]
            product_price = items_tmp[0][4]

        
        #cate
        #print cate_id
        dc = get_full_category(stat_conn,cate_id)     
        cat1 = 0
        cat1name = ''
        cat2 = 0
        cat2name=''
        cat3 = 0
        cat3name=''
        if dc != None:
            if len(dc) > 0:
                cat1 = dc[0][0]
                cat1name = dc[0][1]
            if len(dc) > 1:
                cat2 = dc[1][0]
                cat2name=dc[1][1]
            if len(dc) > 2:
                cat3 = dc[2][0]
                cat3name=dc[2][1]
       
       #city
        city_id = 0
        city_name = ''
        area_id = 0
        area_name = ''
        local_id= 0
        local_name = ''
        if local != None and local !='':
            city = json.loads(local)[0]['L']
            #print city
            dc = get_citys(city)
            if dc != None:
                if len(dc) > 0:
                    city_id = dc[0][0]
                    city_name = dc[0][1]
                if len(dc)>1:
                    area_id = dc[1][0]
                    area_name = dc[1][1]
                if len(dc)>2:
                    local_id= dc[2][0]
                    local_name = dc[2][1]
        
        book_amount= 0
        query_tmp = "select price,turnoverprice from t_orderproduct where orderid=%s and productid=%s"%(order_id,product_id)
        items_tmp = conn.get_results(query_tmp)
        if len(items_tmp)>0:
            product_price = items_tmp[0][0]
            book_amount = items_tmp[0][1]
        
        price = product_price
        if times > 0 :
            if refers.has_key('%s'%order_refer):
                price = product_price/times
            
            
        param = [book_id,buyer_id,shop_id,order_id,order_refer,product_id,product_name,SKUID,book_num,price,book_amount,unit,book_state,operation_time,start_time,end_time,times,book_desc,modify_state,cat1,cat1name,cat2,cat2name,cat3,cat3name,city_id,city_name,area_id,area_name,local_id,local_name]
        line_list.append('\t'.join(map(lambda i : str(i), param)))
        
    cont = '\n'.join(line_list)
    path = "./order_data/"
    file_name = "order_book_%s.txt"%date.replace('-',"")
    writeTextToFile(path,file_name,"w",cont)
    
    query = "truncate table order_book;"
    stat_conn.execute_sql(query)
    load_data(stat_conn,path+file_name,stat_table,'\t')
    conn.close()
    prod_conn.close()
    stat_conn.close()
    #shop_conn.close()
#logic
def execute(date):
    #get_order_data(date)    
    try:
        pass
        get_order_data(date)
    except Exception,e:
        print 'get_order_data:',e
    try:
        pass
        get_order_items_data(date)
    except Exception,e:
        print 'get_order_items_data:',e
    try:
        pass
        get_order_ticket_data(date)
    except Exception,e:
        print 'get_order_ticket_data:',e
    try:
        pass
        get_order_book_data(date)
    except Exception,e:
        print 'get_order_book_data:',e
    
    pass
def main():

   #date = '2012-05-28'
   #execute(date)
   inp = 0
   if len(sys.argv)==1:
        start_date=get_yesterday()
        end_date=start_date
   elif len(sys.argv)==2:
        inp = sys.argv[1]
        start_date=format_date(sys.argv[1])
        end_date=start_date
   elif len(sys.argv)==3:
        start_date=format_date(sys.argv[1])
        end_date=format_date(sys.argv[2])
        
   if inp == "1":
        today = get_today()
        get_order_data(today)
   else:
        stat_date=start_date
        while stat_date>=start_date and stat_date<=end_date:
             print "The program of the date of %s is beginning at %s " %(stat_date,get_now())
             #
             execute( stat_date)
             
             #get_order_data(stat_date)
             print "The program of the date of %s is end at %s " %(stat_date,get_now())
             stat_date=var_date(stat_date)


if __name__=='__main__':
    main()

